Node.js-Backend見聞錄(19)：實作-商品系統(二)-資料庫設計

前言

這篇文章將接續實作-商品系統(一)-設計概觀所提的想法，思考並設計出資料庫大概會是怎麼樣。

資料庫關聯表

若要達成實作-商品系統(一)-設計概觀中的構想，等同於我們需要開一個新的database並新增三個table，分別是：

• database: shopping_cart

• member: 會員資料表，用來存放會員部分的資料。

• product: 商品資料表，用來存放商品部分的資料。

• order_list： 用來記錄哪位顧客在什麼時間買了什麼東西。

各個資料表生所生成的table欄位及欄位型態會是：

member

product

order_list

這部分較為特別的是order_list的table會使用到來自member的id及product的id，而這兩個table的id值在order_list會被稱為是Foreign key(外來鍵)。且我們會額外將order_id, cusomter_id及product_id組合成Composite key(組合鍵)。

手繪關聯表

對不起，筆者寫字不漂亮，還用了不少立可白 Orz

示意表

上述的table若寫入資料後，會像下述這樣呈現。

member

假設目前有三個會員分別是cat, dog, penguin。

product

假設目前有三個商品分別是逗貓棒、潔牙骨跟企鵝玩偶。

order_list

假設會員cat他分別購買了三種商品，分別是：

• 逗貓棒買了2支，共24.00元。
• 潔牙骨買了1個，共5.00元。
• 企鵝玩偶買了一隻，共20.00元。

建置指令

這部分的指令說明可參考關於後端觀念(七)-如何設定資料庫。

建立shopping_cart的資料庫：

mysql> CREATE DATABASE shopping_cart DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

之後給予我們先前設立的帳號使用shopping_cart資料庫的權限：

mysql> GRANT ALL PRIVILEGES ON shopping_cart.* TO 'testing'@'localhost';
Query OK, 0 rows affected (0.04 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

註記：testing為展示用帳號，請自行更改成當初所設定的帳號。

之後，切換成testing的帳號，來編輯shopping_cart資料庫。

$ mysql -u testing -p

進入MySQL後，輸入use shopping_cart指令來切換資料庫。

mysql> use shopping_cart

建立member table

mysql> CREATE TABLE member (
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(30) NOT NULL,
    -> email VARCHAR(30) NOT NULL,
    -> password VARCHAR(200) NOT NULL,
    -> img LONGBLOB,
    -> img_name VARCHAR(20),
    -> update_date DATETIME,
    -> create_date DATETIME NOT NULL);
Query OK, 0 rows affected (0.08 sec)

使用DESCRIBE member指令來觀看整個table的data type：

mysql> DESCRIBE member;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(30)  | NO   |     | NULL    |                |
| email       | varchar(30)  | NO   |     | NULL    |                |
| password    | varchar(200) | NO   |     | NULL    |                |
| img         | longblob     | YES  |     | NULL    |                |
| img_name    | varchar(20)  | YES  |     | NULL    |                |
| update_date | datetime     | YES  |     | NULL    |                |
| create_date | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
8 rows in set (0.03 sec)

建立product table

mysql> CREATE TABLE product (
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(30) NOT NULL,
    -> price DECIMAL(5,2) NOT NULL,
    -> quantity INT(10) NOT NULL,
    -> img LONGBLOB,
    -> img_name VARCHAR(20),
    -> remark VARCHAR(100),
    -> update_date DATETIME,
    -> create_date DATETIME NOT NULL);
Query OK, 0 rows affected (0.05 sec)

使用DESCRIBE product指令來觀看整個table的data type：

mysql> DESCRIBE product;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(30)  | NO   |     | NULL    |                |
| price       | decimal(5,2) | NO   |     | NULL    |                |
| quantity    | int(10)      | NO   |     | NULL    |                |
| img         | longblob     | YES  |     | NULL    |                |
| img_name    | varchar(20)  | YES  |     | NULL    |                |
| remark      | varchar(100) | YES  |     | NULL    |                |
| update_date | datetime     | YES  |     | NULL    |                |
| create_date | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)

建立order_list table

mysql> CREATE TABLE order_list (
    -> order_id INT NOT NULL,
    -> member_id INT NOT NULL,
    -> product_id INT NOT NULL,
    -> order_quantity INT(10) NOT NULL,
    -> order_price DECIMAL(10,2) NOT NULL,
    -> is_complete INT(5) NOT NULL,
    -> update_date DATETIME,
    -> order_date DATETIME NOT NULL,
    -> PRIMARY KEY(order_id, member_id, product_id),
    -> FOREIGN KEY (member_id) REFERENCES member (id),
    -> FOREIGN KEY (product_id) REFERENCES product (id));
Query OK, 0 rows affected (0.03 sec)

使用DESCRIBE order_list指令來觀看整個table的data type：

mysql> DESCRIBE order_list;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| order_id       | int(11)       | NO   | PRI | NULL    |       |
| member_id      | int(11)       | NO   | PRI | NULL    |       |
| product_id     | int(11)       | NO   | PRI | NULL    |       |
| order_quantity | int(10)       | NO   |     | NULL    |       |
| order_price    | decimal(10,2) | NO   |     | NULL    |       |
| is_complete    | int(5)        | NO   |     | NULL    |       |
| update_date    | datetime      | YES  |     | NULL    |       |
| order_date     | datetime      | NO   |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

小結

該部分較為特別的是我們有額外用到Foreign key(外來鍵)及Composite key(組合鍵)，且其order_list table的設計方式也不只有筆者的這種設計方式，讀者也可以試著去修改看看想要呈現的方式。在下篇我們就接續進入到寫程式的階段。